package com.example.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * Excel操作类
 *
 * @author zyh
 * @create 2024-09-09
 */
@Slf4j
public class PoiUtil {

    /**
     * 2003版本
     */
    public static final String VERSION_2003 = "2003";

    /**
     * 2007以及已上版本
     */
    public static final String VERSION_OVER_2007 = "2007";

    private Workbook workbook;
    private OutputStream os;
    private String pattern;// 日期格式

    /**
     * 日期的格式
     *
     * @param pattern 日期的格式
     */
    public void setPattern(String pattern) {
        this.pattern = pattern;
    }

    /**
     * 构造方法
     *
     * @param version 版本
     */
    public PoiUtil(String version) {
        if (VERSION_2003.equals(version)) {
            workbook = new HSSFWorkbook();
        } else {
            workbook = new XSSFWorkbook();
        }
    }

    private void setTextStyle(Workbook wb, Sheet sheet, int column) {
        CellStyle style = wb.createCellStyle();
        DataFormat excelFormat = wb.createDataFormat();
        style.setWrapText(true);
        style.setDataFormat(excelFormat.getFormat("@"));
        sheet.setDefaultColumnStyle(column, style);
    }

    /**
     * 构造方法
     *
     * @param is      输入流
     * @param version 版本
     * @throws Exception 异常 异常
     */
    public PoiUtil(InputStream is, String version) throws Exception {
        if (VERSION_2003.equals(version)) {
            workbook = new HSSFWorkbook(is);
        } else {
            workbook = new XSSFWorkbook(is);
        }
    }

    /**
     * 读取 Excel 第一页所有数据
     *
     * @return List<List < String>> List<List<String>
     * @throws Exception 异常 异常
     */
    public List<List<String>> read() throws Exception {
        return read(0, 0, getRowCount(0) - 1);
    }

    /**
     * 读取指定sheet 页所有数据
     *
     * @param sheetIx 指定 sheet 页，从 0 开始
     * @return List<List < String>>
     * @throws Exception 异常
     */
    public List<List<String>> read(int sheetIx) throws Exception {
        return read(sheetIx, 0, getRowCount(sheetIx) - 1);
    }

    /**
     * 读取指定sheet 页指定行数据
     *
     * @param sheetIx 指定 sheet 页，从 0 开始
     * @param start   指定开始行，从 0 开始
     * @param end     指定结束行，从 0 开始
     * @return List<List < String>>
     * @throws Exception 异常
     */
    public List<List<String>> read(int sheetIx, int start, int end) throws Exception {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        List<List<String>> list = new ArrayList<List<String>>();
        if (end > getRowCount(sheetIx)) {
            end = getRowCount(sheetIx);
        }
        int cols = sheet.getRow(0).getLastCellNum(); // 第一行总列数
        for (int i = start; i <= end; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<String> rowList = new ArrayList<String>();
            boolean isHasValue = false;
            for (int j = 0; j < cols; j++) {
                String cellValue = getCellValueByCell(row.getCell(j));
                if (!isHasValue && cellValue != null && cellValue.trim().length() > 0) {
                    isHasValue = true;
                }
                rowList.add(cellValue);
            }
            if (isHasValue) {
                list.add(rowList);
            }
        }
        return list;
    }

    // 获取单元格各类型值，返回字符串类型
    public static String getCellValueByCell(Cell cell) {
        // 判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                } else {
                    BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
//			cellValue = cell.getCellFormula();
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }


    /**
     * 将数据写入到 Excel 新创建的 Sheet 页
     *
     * @param filePath  文件路径
     * @param rowData   数据
     * @param sheetName 长度为1-31，不能包含后面任一字符: ：\ / ? * [ ]
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean write(String filePath, List<List<String>> rowData, String sheetName) throws IOException {
        Sheet sheet = null;
        if (sheetName == null || sheetName.trim().length() == 0) {
            sheet = workbook.createSheet();
        } else {
            sheet = workbook.createSheet(sheetName.trim());
        }
        int sheetIx = workbook.getSheetIndex(sheet);
        return write(filePath, sheetIx, rowData, 0);
    }

    /**
     * 将数据追加到sheet页最后
     *
     * @param filePath 文件路径
     * @param rowData  数据
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param isAppend 是否追加,true 追加，false 重置sheet再添加
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean write(String filePath, int sheetIx, List<List<String>> rowData, boolean isAppend) throws IOException {
        if (isAppend) {
            return write(filePath, sheetIx, rowData, getRowCount(sheetIx));
        } else {// 清空再添加
            clearSheet(sheetIx);
            return write(filePath, sheetIx, rowData, 0);
        }
    }

    /**
     * 将数据写入到 Excel 指定 Sheet 页指定开始行中,指定行后面数据向后移动
     *
     * @param filePath 文件路径
     * @param rowData  数据
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param startRow 指定开始行，从 0 开始
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean write(String filePath, int sheetIx, List<List<String>> rowData, int startRow) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        FileOutputStream out = null;
        int dataSize = rowData.size();
        if (getRowCount(sheetIx) > 0) {// 如果小于等于0，则一行都不存在
            sheet.shiftRows(startRow, getRowCount(sheetIx), dataSize);
        }
        for (int i = 0; i < dataSize; i++) {
            Row row = sheet.createRow(i + startRow);
            for (int j = 0; j < rowData.get(i).size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(rowData.get(i).get(j) + "");
            }
        }
        out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
        return true;
    }

    public boolean export(String filePath) throws IOException {
        FileOutputStream out = null;
        out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
        return true;
    }

    /**
     * 将数据写入到 Excel 指定 Sheet 页指定开始行中,指定行后面数据向后移动
     *
     * @param sheetName 指定 Sheet 名称
     * @param rowDatas  数据
     * @throws IOException 异常
     */
    public boolean write(String sheetName, List<DataExcel> rowDatas) {
        Sheet sheet = getSheet(sheetName);
        XSSFCellStyle titleStyle = (XSSFCellStyle) createTitleStyle();
        XSSFCellStyle dataStyle = (XSSFCellStyle) createDataStyle();
        for (DataExcel dataExcel : rowDatas) {
            for (int i = 0; i < dataExcel.getRowCount(); i++) {
                Row row = sheet.getRow(dataExcel.getStartRow() + i);
                if (row == null) {
                    row = sheet.createRow(dataExcel.getStartRow() + i);
                }
                if (dataExcel.getStartRow() == 1) {
                    row.setHeight((short) (15.625 * 40));
                } else {
                    row.setHeight((short) (15.625 * 30));
                }
                for (int j = 0; j < dataExcel.getColCount(); j++) {
                    Cell cell = row.getCell(dataExcel.getStartCol() + j);
                    if (cell == null) {
                        cell = row.createCell(dataExcel.getStartCol() + j);
                    }
                    cell.setCellValue(dataExcel.getValue() + "");
                    sheet.setColumnWidth(dataExcel.getStartCol() + j, 50 * 100);
                    if (i == 0 && j == 0) {
                        cell.setCellStyle(titleStyle);
                    } else {
                        cell.setCellStyle(dataStyle);
                    }
                }

            }
        }
        for (DataExcel dataExcel : rowDatas) {
            if (dataExcel.getRowCount() == 1 && dataExcel.getColCount() == 1) {
                continue;
            }
            try {
                CellRangeAddress region = new CellRangeAddress(dataExcel.getStartRow(),
                        dataExcel.getStartRow() + dataExcel.getRowCount() - 1, dataExcel.getStartCol(), dataExcel.getStartCol() + dataExcel.getColCount() - 1);
                sheet.addMergedRegion(region);
            } catch (Exception e) {
                System.out.println(dataExcel.toString());
                e.printStackTrace();
            }

        }
        return true;
    }

    public boolean write2003(String sheetName, List<DataExcel> rowDatas) {
        Sheet sheet = getSheet(sheetName);
        HSSFCellStyle titleStyle = (HSSFCellStyle) createTitleStyle2003();
        HSSFCellStyle dataStyle = (HSSFCellStyle) createDataStyle2003();
        for (DataExcel dataExcel : rowDatas) {
            for (int i = 0; i < dataExcel.getRowCount(); i++) {
                Row row = sheet.getRow(dataExcel.getStartRow() + i);
                if (row == null) {
                    row = sheet.createRow(dataExcel.getStartRow() + i);
                }
                if (dataExcel.getStartRow() == 1) {
                    row.setHeight((short) (15.625 * 40));
                } else {
                    row.setHeight((short) (15.625 * 30));
                }
                for (int j = 0; j < dataExcel.getColCount(); j++) {
                    Cell cell = row.getCell(dataExcel.getStartCol() + j);
                    if (cell == null) {
                        cell = row.createCell(dataExcel.getStartCol() + j);
                    }
                    cell.setCellValue(dataExcel.getValue() + "");
                    sheet.setColumnWidth(dataExcel.getStartCol() + j, 50 * 100);
                    if (i == 0 && j == 0) {
                        cell.setCellStyle(titleStyle);
                    } else {
                        cell.setCellStyle(dataStyle);
                    }
                }

            }
        }
        for (DataExcel dataExcel : rowDatas) {
            if (dataExcel.getRowCount() == 1 && dataExcel.getColCount() == 1) {
                continue;
            }
            try {
                CellRangeAddress region = new CellRangeAddress(dataExcel.getStartRow(),
                        dataExcel.getStartRow() + dataExcel.getRowCount() - 1, dataExcel.getStartCol(), dataExcel.getStartCol() + dataExcel.getColCount() - 1);
                sheet.addMergedRegion(region);
            } catch (Exception e) {
                System.out.println(dataExcel.toString());
                e.printStackTrace();
            }

        }
        return true;
    }

    private Sheet getSheet(String sheetName) {
        Sheet sheet = null;
        if (sheetName == null || sheetName.trim().length() == 0) {
            sheet = workbook.createSheet();
        } else {
            sheet = workbook.getSheet(sheetName.trim());
            if (sheet == null) {
                sheet = workbook.createSheet(sheetName.trim());
            }
        }
        return sheet;
    }

    private CellStyle createTitleStyle2003() {
        HSSFCellStyle style = (HSSFCellStyle) workbook.createCellStyle();
        // 设置上下左右四个边框宽度
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 单元格对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置字体格式
        HSSFFont titleFont = (HSSFFont) workbook.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 14);
//        titleFont.setColor(blackColorIndex); // 使用设置的颜色索引
        titleFont.setBold(true);
        titleFont.setItalic(false);
        titleFont.setStrikeout(false);
        titleFont.setUnderline(Font.U_NONE);

        // 将字体格式设置到HSSFCellStyle上
        style.setFont(titleFont);
        return style;
    }

    private CellStyle createDataStyle2003() {
        HSSFCellStyle style = (HSSFCellStyle) workbook.createCellStyle();

        //设置上下左右四个边框宽度
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        style.setWrapText(true);

        //单元格对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置字体格式
        HSSFFont titleFont = (HSSFFont) workbook.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 10);
//        titleFont.setColor(new XSSFColor(java.awt.Color.BLACK));
        titleFont.setBold(false);
        titleFont.setItalic(false);
        titleFont.setStrikeout(false);
        titleFont.setUnderline((byte) 0);
        //将字体格式设置到HSSFCellStyle上
        style.setFont(titleFont);
        return style;
    }

    private CellStyle createTitleStyle() {
        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        //设置上下左右四个边框宽度
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        //设置上下左右四个边框颜色
        style.setTopBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setBottomBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setLeftBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setRightBorderColor(new XSSFColor(java.awt.Color.BLACK));

        //单元格对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格背景色
//        style.setFillForegroundColor(new XSSFColor(java.awt.Color.cyan));
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //设置字体格式
        XSSFFont titleFont = (XSSFFont) workbook.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(new XSSFColor(java.awt.Color.BLACK));
        titleFont.setBold(true);
        titleFont.setItalic(false);
        titleFont.setStrikeout(false);
        titleFont.setUnderline((byte) 0);
        //将字体格式设置到HSSFCellStyle上
        style.setFont(titleFont);
        return style;
    }

    private CellStyle createDataStyle() {
        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();

        //设置上下左右四个边框宽度
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        style.setWrapText(true);
        //设置上下左右四个边框颜色
        style.setTopBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setBottomBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setLeftBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setRightBorderColor(new XSSFColor(java.awt.Color.BLACK));

        //单元格对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格背景色
//        style.setFillForegroundColor(new XSSFColor(java.awt.Color.cyan));
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);


        //设置字体格式
        XSSFFont titleFont = (XSSFFont) workbook.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 10);
        titleFont.setColor(new XSSFColor(java.awt.Color.BLACK));
        titleFont.setBold(false);
        titleFont.setItalic(false);
        titleFont.setStrikeout(false);
        titleFont.setUnderline((byte) 0);
        //将字体格式设置到HSSFCellStyle上
        style.setFont(titleFont);
        return style;
    }

    private CellStyle createDataLeftAlignStyle() {
        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();

        //设置上下左右四个边框宽度
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        style.setWrapText(true);
        //设置上下左右四个边框颜色
        style.setTopBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setBottomBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setLeftBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setRightBorderColor(new XSSFColor(java.awt.Color.BLACK));

        //单元格对齐方式
//        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格背景色
//        style.setFillForegroundColor(new XSSFColor(java.awt.Color.cyan));
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);


        //设置字体格式
        XSSFFont titleFont = (XSSFFont) workbook.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 10);
        titleFont.setColor(new XSSFColor(java.awt.Color.BLACK));
        titleFont.setBold(false);
        titleFont.setItalic(false);
        titleFont.setStrikeout(false);
        titleFont.setUnderline((byte) 0);
        //将字体格式设置到HSSFCellStyle上
        style.setFont(titleFont);
        return style;
    }

    /**
     * 设置cell 样式
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定行，从 0 开始
     * @param colIndex 指定列，从 0 开始
     * @param style    CellStyle
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean setStyle(int sheetIx, int rowIndex, int colIndex, CellStyle style) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        // sheet.autoSizeColumn(colIndex, true);// 设置列宽度自适应
        sheet.setColumnWidth(colIndex, 4000);

        Cell cell = sheet.getRow(rowIndex).getCell(colIndex);
        cell.setCellStyle(style);

        return true;
    }

    /**
     * 设置样式
     *
     * @param type 1：标题 2：第一行
     * @return List<List < String>>
     */
    public CellStyle makeStyle(int type) {
        CellStyle style = workbook.createCellStyle();

        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));// // 内容样式 设置单元格内容格式是文本
        style.setAlignment(HorizontalAlignment.CENTER);// 内容居中

        // style.setBorderTop(CellStyle.BORDER_THIN);// 边框样式
        // style.setBorderRight(CellStyle.BORDER_THIN);
        // style.setBorderBottom(CellStyle.BORDER_THIN);
        // style.setBorderLeft(CellStyle.BORDER_THIN);

        Font font = workbook.createFont();// 文字样式

        if (type == 1) {
            // style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);//颜色样式
            // 前景颜色
            // style.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);//背景色
            // style.setFillPattern(CellStyle.ALIGN_FILL);// 填充方式
            font.setBold(true);
            font.setFontHeight((short) 500);
        }

        if (type == 2) {
            font.setBold(true);
            font.setFontHeight((short) 300);
        }

        style.setFont(font);

        return style;
    }

    /**
     * 合并单元格
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param firstRow 开始行
     * @param lastRow  结束行
     * @param firstCol 开始列
     * @param lastCol  结束列
     */
    public void region(int sheetIx, int firstRow, int lastRow, int firstCol, int lastCol) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 指定行是否为空
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定开始行，从 0 开始
     * @return List<List < String>> true 不为空，false 不行为空
     * @throws IOException 异常
     */
    public boolean isRowNull(int sheetIx, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        return sheet.getRow(rowIndex) == null;
    }

    /**
     * 创建行，若行存在，则清空
     *
     * @param sheetIx  指定 sheet 页，从 0 开始
     * @param rowIndex 指定创建行，从 0 开始
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean createRow(int sheetIx, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.createRow(rowIndex);
        return true;
    }

    /**
     * 指定单元格是否为空
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定开始行，从 0 开始
     * @param colIndex 指定开始列，从 0 开始
     * @return List<List < String>> true 行不为空，false 行为空
     * @throws IOException 异常
     */
    public boolean isCellNull(int sheetIx, int rowIndex, int colIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        if (!isRowNull(sheetIx, rowIndex)) {
            return false;
        }
        Row row = sheet.getRow(rowIndex);
        return row.getCell(colIndex) == null;
    }

    /**
     * 创建单元格
     *
     * @param sheetIx  指定 sheet 页，从 0 开始
     * @param rowIndex 指定行，从 0 开始
     * @param colIndex 指定创建列，从 0 开始
     * @return List<List < String>> true 列为空，false 行不为空
     * @throws IOException 异常
     */
    public boolean createCell(int sheetIx, int rowIndex, int colIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        row.createCell(colIndex);
        return true;
    }

    /**
     * 返回sheet 中的行数
     *
     * @param sheetIx 指定 Sheet 页，从 0 开始
     * @return List<List < String>>
     */
    public int getRowCount(int sheetIx) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        if (sheet.getPhysicalNumberOfRows() == 0) {
            return 0;
        }
        return sheet.getLastRowNum() + 1;

    }

    /**
     * 返回所在行的列数
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定行，从0开始
     * @return List<List < String>> 返回-1 表示所在行为空
     */
    public int getColumnCount(int sheetIx, int rowIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        return row == null ? -1 : row.getLastCellNum();

    }

    /**
     * 设置row 和 column 位置的单元格值
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定行，从0开始
     * @param colIndex 指定列，从0开始
     * @param value    值
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean setValueAt(int sheetIx, int rowIndex, int colIndex, String value) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.getRow(rowIndex).getCell(colIndex).setCellValue(value);
        return true;
    }

    /**
     * 返回 row 和 column 位置的单元格值
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定行，从0开始
     * @param colIndex 指定列，从0开始
     * @return List<List < String>>
     */
    public String getValueAt(int sheetIx, int rowIndex, int colIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        return getCellValueToString(sheet.getRow(rowIndex).getCell(colIndex));
    }

    /**
     * 重置指定行的值
     *
     * @param rowData  数据
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定行，从0开始
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean setRowValue(int sheetIx, List<String> rowData, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        for (int i = 0; i < rowData.size(); i++) {
            row.getCell(i).setCellValue(rowData.get(i));
        }
        return true;
    }

    /**
     * 返回指定行的值的集合
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定行，从0开始
     * @return List<List < String>>
     */
    public List<String> getRowValue(int sheetIx, int rowIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        List<String> list = new ArrayList<String>();
        if (row == null) {
            list.add(null);
        } else {
            for (int i = 0; i < row.getLastCellNum(); i++) {
                list.add(getCellValueToString(row.getCell(i)));
            }
        }
        return list;
    }

    /**
     * 返回列的值的集合
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定行，从0开始
     * @param colIndex 指定列，从0开始
     * @return List<List < String>>
     */
    public List<String> getColumnValue(int sheetIx, int rowIndex, int colIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        List<String> list = new ArrayList<String>();
        for (int i = rowIndex; i < getRowCount(sheetIx); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                list.add(null);
                continue;
            }
            list.add(getCellValueToString(sheet.getRow(i).getCell(colIndex)));
        }
        return list;
    }

    /**
     * 获取excel 中sheet 总页数
     *
     * @return List<List < String>>
     */
    public int getSheetCount() {
        return workbook.getNumberOfSheets();
    }

    /**
     * 获取 sheet名称
     *
     * @param sheetIx 指定 Sheet 页，从 0 开始
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public String getSheetName(int sheetIx) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        return sheet.getSheetName();
    }

    /**
     * 获取sheet的索引，从0开始
     *
     * @param name sheet 名称
     * @return List<List < String>> -1表示该未找到名称对应的sheet
     */
    public int getSheetIndex(String name) {
        return workbook.getSheetIndex(name);
    }

    /**
     * 删除指定sheet
     *
     * @param sheetIx 指定 Sheet 页，从 0 开始
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean removeSheetAt(int sheetIx) throws IOException {
        workbook.removeSheetAt(sheetIx);
        return true;
    }

    /**
     * 删除指定sheet中行，改变该行之后行的索引
     *
     * @param sheetIx  指定 Sheet 页，从 0 开始
     * @param rowIndex 指定行，从0开始
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean removeRow(int sheetIx, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.shiftRows(rowIndex + 1, getRowCount(sheetIx), -1);
        Row row = sheet.getRow(getRowCount(sheetIx) - 1);
        sheet.removeRow(row);
        return true;
    }

    /**
     * 设置sheet 页的索引
     *
     * @param sheetname Sheet 名称
     * @param sheetIx   Sheet 索引，从0开始
     */
    public void setSheetOrder(String sheetname, int sheetIx) {
        workbook.setSheetOrder(sheetname, sheetIx);
    }

    /**
     * 清空指定sheet页（先删除后添加并指定sheetIx）
     *
     * @param sheetIx 指定 Sheet 页，从 0 开始
     * @return List<List < String>>
     * @throws IOException 异常
     */
    public boolean clearSheet(int sheetIx) throws IOException {
        String sheetname = getSheetName(sheetIx);
        removeSheetAt(sheetIx);
        workbook.createSheet(sheetname);
        setSheetOrder(sheetname, sheetIx);
        return true;
    }

    /**
     * 关闭流
     *
     * @throws IOException 异常
     */
    public void close() throws IOException {
        if (os != null) {
            os.close();
        }
        workbook.close();
    }

    /**
     * 转换单元格的类型为String 默认的 <br>
     * 默认的数据类型：CELL_TYPE_BLANK(3), CELL_TYPE_BOOLEAN(4),
     * CELL_TYPE_ERROR(5),CELL_TYPE_FORMULA(2), CELL_TYPE_NUMERIC(0),
     * CELL_TYPE_STRING(1)
     *
     * @param cell
     * @return String
     */
    private String getCellValueToString(Cell cell) {
        String strCell = "";
        if (cell == null) {
            return null;
        }
        CellType cellTypeEnum = cell.getCellTypeEnum();
        if (cellTypeEnum == CellType.BOOLEAN) {
            strCell = String.valueOf(cell.getBooleanCellValue());
        } else if (cellTypeEnum == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                if (pattern != null) {
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    strCell = sdf.format(date);
                } else {
                    strCell = date.toString();
                }
            }
            // 不是日期格式，则防止当数字过长时以科学计数法显示
            cell.setCellType(CellType.STRING);
            strCell = cell.toString();
        } else if (cellTypeEnum == CellType.STRING) {
            strCell = cell.getStringCellValue();
        }
        return strCell;
    }

    public static boolean isEmptyRow(Row row) {
        if (row == null || row.toString().isEmpty()) {
            return true;
        } else {
            Iterator<Cell> it = row.iterator();
            boolean isEmpty = true;
            while (it.hasNext()) {
                Cell cell = it.next();
                if (cell.getCellTypeEnum() != CellType.BLANK) {
                    isEmpty = false;
                    break;
                }
            }
            return isEmpty;
        }
    }

    public void draw(String sheetName, List<DrawShapeCell> drawShapeCellData) {
        Sheet sheet = getSheet(sheetName);
        XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();
        // 获取绘图对象
        for (DrawShapeCell shapeCell : drawShapeCellData) {
            double columnWidthEMU = (sheet.getColumnWidth(shapeCell.getCol1()) * 256) * 1.2;
            Row row = sheet.getRow(shapeCell.getRow1());
            if (row == null) {
                row = sheet.createRow(shapeCell.getRow1());
            }
            int rowHeightEMU = Units.toEMU(row.getHeightInPoints());
            //参数检查
            for (DrawShapeCell data : drawShapeCellData) {
                data.setCol1(data.getCol1() + (int) Math.floor(data.getDx1()));
                data.setDx1(data.getDx1() - Math.floor(data.getDx1()));
                data.setRow1(data.getRow1() + (int) Math.floor(data.getDy1()));
                data.setDy1(data.getDy1() - Math.floor(data.getDy1()));
                data.setCol2(data.getCol2() + (int) Math.floor(data.getDx2()));
                data.setDx2(data.getDx2() - Math.floor(data.getDx2()));
                data.setRow2(data.getRow2() + (int) Math.floor(data.getDy2()));
                data.setDy2(data.getDy2() - Math.floor(data.getDy2()));
            }
            // 创建第一个矩形形状
            XSSFClientAnchor anchor = drawing.createAnchor(
                    (int) Math.round(columnWidthEMU * shapeCell.getDx1()),
                    (int) Math.round(rowHeightEMU * shapeCell.getDy1()),
                    (int) Math.round(columnWidthEMU * shapeCell.getDx2()),
                    (int) Math.round(rowHeightEMU * shapeCell.getDy2()),
                    shapeCell.getCol1(),
                    shapeCell.getRow1(),
                    shapeCell.getCol2(),
                    shapeCell.getRow2());
            XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
            shape.setText(shapeCell.getText());
            shape.setShapeType(shapeCell.getShapeTypes());
            // 这里我们简单设置填充色为黄色
            shape.setFillColor(shapeCell.getColor().getRed(), shapeCell.getColor().getGreen(), shapeCell.getColor().getBlue());
        }
    }

    public void draw2003(String sheetName, List<DrawShapeCell> drawShapeCellData) {
        Sheet sheet = getSheet(sheetName);

        // 获取绘图对象
        HSSFPatriarch drawing = (HSSFPatriarch) sheet.createDrawingPatriarch();

        for (DrawShapeCell shapeCell : drawShapeCellData) {
            double columnWidthEMU = (sheet.getColumnWidth(shapeCell.getCol1()) * 256);
            Row row = sheet.getRow(shapeCell.getRow1());
            if (row == null) {
                row = sheet.createRow(shapeCell.getRow1());
                // 设置行高（默认行高）
                row.setHeightInPoints(12.75F); // 例如：设置行高的点数
            }
            int rowHeightEMU = Units.toEMU(row.getHeightInPoints());

            // 参数检查与调整
            shapeCell.setCol1(shapeCell.getCol1() + (int) Math.floor(shapeCell.getDx1()));
            shapeCell.setDx1(shapeCell.getDx1() - Math.floor(shapeCell.getDx1()));
            shapeCell.setRow1(shapeCell.getRow1() + (int) Math.floor(shapeCell.getDy1()));
            shapeCell.setDy1(shapeCell.getDy1() - Math.floor(shapeCell.getDy1()));
            shapeCell.setCol2(shapeCell.getCol2() + (int) Math.floor(shapeCell.getDx2()));
            shapeCell.setDx2(shapeCell.getDx2() - Math.floor(shapeCell.getDx2()));
            shapeCell.setRow2(shapeCell.getRow2() + (int) Math.floor(shapeCell.getDy2()));
            shapeCell.setDy2(shapeCell.getDy2() - Math.floor(shapeCell.getDy2()));

            // 创建锚点，test，todo：添加2003版本图形条位置计算
            HSSFClientAnchor anchor = new HSSFClientAnchor(
                    (int) Math.round(1023 * shapeCell.getDx1()),
                    (int) Math.round(255 * shapeCell.getDy1()),
                    (int) Math.round(1023 * shapeCell.getDx2()),
                    (int) Math.round(255 * shapeCell.getDy2()),
                    (short) shapeCell.getCol1(),
                    shapeCell.getRow1(),
                    (short) shapeCell.getCol2(),
                    shapeCell.getRow2()
            );

            // 创建一个简单的形状
            HSSFSimpleShape shape = drawing.createSimpleShape(anchor);

            // 设置文本（HSSF 不支持在 HSSFSimpleShape 上直接设置文本）
            // 你可以选择创建 HSSFTextBox 来添加文本标签或其他替代方案
            // 这里我们只设置填充颜色和形状类型
            shape.setFillColor(shapeCell.getColor().getRed(), shapeCell.getColor().getGreen(), shapeCell.getColor().getBlue());
            shape.setLineStyle(HSSFSimpleShape.LINESTYLE_SOLID);
            shape.setLineWidth(2);
        }
    }
}
